/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package accessor;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import model.Model;

/**
 *
 * @author QUYET
 */
public class HomeProductAccessor extends Model {
    public List<Products> getTopNewProduct(String key,int limit){
        String lim = "";
        if(limit>0){
            lim =  " top "+String.valueOf(limit);
        }
        List<Products> productList = new ArrayList<Products>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT "+lim+" productID,productName,defaultCategoryID,quantity,defaultImage,price,description FROM tblProducts ";
            if(key!=null){
                sql+=" Where productName LIKE '%"+key+"%'";
            } else{
                sql+=" Order By productID DESC";
            }
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {;
                Products products = new Products();
                products.setProductID(rs.getInt("productID"));
                products.setProductName(rs.getString("productName"));
                products.setDefaultCategoryID(rs.getInt("defaultCategoryID"));
                products.setQuantity(rs.getInt("quantity"));
                products.setDefaultImage(rs.getString("defaultImage"));
                products.setPrice(rs.getDouble("price"));
                products.setDescription(rs.getString("description"));
                productList.add(products);
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return productList;
        } catch (Exception ex) {
            ex.printStackTrace();
            return new ArrayList<Products>();
        }
    }
    public List<Products> getFeatureProduct(int limit){
        List<Products> productList = new ArrayList<Products>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT TOP "+limit+" productID,productName,defaultCategoryID,quantity,defaultImage,price,description FROM tblProducts Order by qtySoldLog ";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {;
                Products products = new Products();
                products.setProductID(rs.getInt("productID"));
                products.setProductName(rs.getString("productName"));
                products.setDefaultCategoryID(rs.getInt("defaultCategoryID"));
                products.setQuantity(rs.getInt("quantity"));
                products.setDefaultImage(rs.getString("defaultImage"));
                products.setPrice(rs.getDouble("price"));
                products.setDescription(rs.getString("description"));
                productList.add(products);
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return productList;
        } catch (Exception ex) {
            ex.printStackTrace();
            return new ArrayList<Products>();
        }
    }
    
    public List<Products> getByCategories(int categoryID) {
        List<Products> productList = new ArrayList<Products>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT tblProducts.productID, tblProducts.productName,tblProducts.defaultCategoryID,tblProducts.quantity,tblProducts.defaultImage,tblProducts.price,tblProducts.description ";
            sql+=" FROM tblProducts INNER JOIN tblProductCategory ON tblProducts.productID = tblProductCategory.productID ";
            if(categoryID>0){
                sql+=" WHERE tblProductCategory.CategoryID=? ";
            }
            ps = conn.prepareStatement(sql);
            if(categoryID>0){
                ps.setInt(1, categoryID);
            }
            rs = ps.executeQuery();
            while (rs.next()) {;
                Products products = new Products();
                products.setProductID(rs.getInt("productID"));
                products.setProductName(rs.getString("productName"));
                products.setDefaultCategoryID(rs.getInt("defaultCategoryID"));
                products.setQuantity(rs.getInt("quantity"));
                products.setDefaultImage(rs.getString("defaultImage"));
                products.setPrice(rs.getDouble("price"));
                products.setDescription(rs.getString("description"));
                productList.add(products);
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return productList;
        } catch (Exception ex) {
            ex.printStackTrace();
            return new ArrayList<Products>();
        }
    }
    public Products getProductDetails(int productID){
        Products product = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT tblProducts.productID, tblProducts.productName,tblProducts.defaultCategoryID,tblProducts.quantity,tblProducts.defaultImage,tblProducts.price,tblProducts.description from tblProducts Where productID = ?";
            ps = conn.prepareStatement(sql);
                ps.setInt(1, productID);
            rs = ps.executeQuery();
            while (rs.next()) {;
                product = new Products();
                product.setProductID(rs.getInt("productID"));
                product.setProductName(rs.getString("productName"));
                product.setDefaultCategoryID(rs.getInt("defaultCategoryID"));
                product.setQuantity(rs.getInt("quantity"));
                product.setDefaultImage(rs.getString("defaultImage"));
                product.setPrice(rs.getDouble("price"));
                product.setDescription(rs.getString("description"));
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return product;
        } catch (Exception ex) {
            ex.printStackTrace();
            return new Products();
        }
    }
}
